#!/usr/bin/env python2.5

##############################################################################################
#
#    LocateME! - Finds location of user in a specific building using contextual information
#    Copyright (C) <2009>  <Kattie Tay>
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>
#
##############################################################################################


# Import packages
import sqlite3
import re


# Initialization of global variables
db_conn = None
db_cur = None


# Initialize database
def db_init():
    global db_conn, db_cur
    
    # Connect to the database
    # Create if it does not exist
    db_conn = sqlite3.connect('LocateMe_db.db')
    db_cur = db_conn.cursor()
    
    # Create the following tables if they do not exist
    # location table
    db_cur.execute("create table if not exists location(\
                    name text primary key, \
                    essid text)")

    # gps_data table
    db_cur.execute("create table if not exists gps_data(\
                    loc_name text, \
                    geocode text, \
                    primary key(loc_name,geocode), \
                    foreign key(loc_name) references location(loc_name) \
                    on delete cascade)")
    
    # scan_pts table
    db_cur.execute("create table if not exists scan_pts(\
                    loc_name text, \
                    desc text, \
                    iap_list text, \
                    light_int text, \
                    primary key(loc_name,desc), \
                    foreign key(loc_name) references location(loc_name) \
                    on delete cascade)")
    
    # Create trigger to enforce foreign key constraints
    db_cur.execute("create trigger if not exists delete_loc \
                    before delete on location\
                    begin \
                        delete from gps_data where loc_name = old.name; \
                        delete from scan_pts where loc_name = old.name; \
                    end;") 
    
    
# Add new scan point to scan_pts table corresponding to specified location
def db_add_new_scanpt(loc_name, desc, iap_list, light_int):
    global db_conn, db_cur
    iap_list_str = db_convert_list_to_str(iap_list)
    db_cur.execute("insert into scan_pts values (?,?,?,?)", (loc_name, desc, iap_list_str, light_int))
    db_conn.commit()
    

# Add new location name to location and gps_data tables
def db_add_new_loc(loc_name, geocode, essid):
    global db_conn, db_cur
    db_cur.execute("insert into location values (?,?)", (loc_name, essid))
    db_cur.execute("insert into gps_data values (?,?)", (loc_name, geocode))
    db_conn.commit()
    

# Add geocode values to specified location    
def db_add_geocode_to_loc(loc_name, geocode):  
    global db_conn, db_cur
    db_cur.execute("insert into gps_data values (?,?)", (loc_name, geocode))
    db_conn.commit()
    

# Edit data of specified location and description in scan_pts table
def db_edit_scanpt_details(loc_name, desc, iap_list, light_int):
    global db_conn, db_cur
    iap_list_str = db_convert_list_to_str(iap_list)
    db_cur.execute("update scan_pts set iap_list=?,light_int=? where loc_name=? and desc=?", (iap_list_str, light_int, loc_name, desc))
    db_conn.commit()
    

# Remove specified geocode value from gps_data table
def db_remove_geocode(loc_name, geocode):
    global db_conn, db_cur
    del_loc = False
    db_cur.execute("delete from gps_data where geocode=?", (geocode,))
    # Check if all geocode values corresponding to specified location have been deleted
    # If yes, delete specified location from location table and return True
    exists = db_cur.execute("select geocode from gps_data where loc_name=?", (loc_name,)).fetchone()
    if exists == None:
        db_cur.execute("delete from location where name=?", (loc_name,))
        del_loc = True
    db_conn.commit()
    return del_loc
    

# Remove specified location from location table
def db_remove_loc(loc_name):
    global db_conn, db_cur
    db_cur.execute("delete from location where name=?", (loc_name,))
    db_conn.commit()
    

# Remove specified scan point from scan_pts table
def db_remove_desc(desc):
    global db_conn, db_cur
    db_cur.execute("delete from scan_pts where desc=?", (desc,))
    db_conn.commit()


# Get scan point details of specified location and description
def db_get_desc_details(loc_name, desc):
    global db_cur
    details = db_cur.execute("select iap_list,light_int from scan_pts where loc_name=? and desc=?", (loc_name, desc)).fetchone()
    if not details == None:
        iap_list = details[0]
        light_int = details[1]
        iap_list2 = db_convert_str_to_list(iap_list)
#        print "iap %s light int %s" %(iap_list2, light_int)
        return iap_list2, light_int


# Get list of all iaps and light intensity value corresponding to specified location
def db_get_loc_iap_lightint_list(loc_name):
    global db_cur
    all_iap_list = []
    all_iap_list2 = []
    light_int_list =[]
    db_cur.execute("select iap_list,light_int from scan_pts where loc_name=?", (loc_name,))
    for row in db_cur:
        all_iap_list.append(row[0])
        light_int_list.append(row[1])

    for i in range(0, len(all_iap_list)):
        all_iap_list2.append(db_convert_str_to_list(all_iap_list[i]))
        
#    print "all %s light int %s" %(all_iap_list2, light_int_list)
    return all_iap_list2, light_int_list     
    

# Get list of all scan points corresponding to specified location
def db_get_loc_desc_list(loc_name):
    global db_cur
    desc_list = []
    db_cur.execute("select desc from scan_pts where loc_name=?", (loc_name,))
    for row in db_cur:
        desc_list.append(row[0])
#    print "desc list %s" %desc_list
    return desc_list


# Get list of all geocode values corresponding to specified location
def db_get_loc_geocode_list(loc_name):
    global db_cur
    geocodes_list = []
    db_cur.execute("select geocode from gps_data where loc_name=?", (loc_name,))
    for row in db_cur:
        geocodes_list.append(row[0])
#    print geocodes_list
    return geocodes_list


# Get geocode value corresponding to specified location (just one)
def db_get_loc_geocode(loc_name):
    global db_cur
    geocode = db_cur.execute("select geocode from gps_data where loc_name=?", (loc_name,)).fetchone()
    if not geocode == None:
        return geocode[0]


# Get essid vale corresponding to specified location
def db_get_loc_essid(loc_name):
    global db_cur
    essid = db_cur.execute("select essid from location where name=?", (loc_name,)).fetchone()
    if not essid == None:
        return essid[0]
    

# Get location corresponding to specified geocode
def db_get_loc_name(geocode):
    global db_cur
    loc_name = db_cur.execute("select loc_name from gps_data where geocode=?", (geocode,)).fetchone()
    if loc_name == None:
        loc_name = "Unknown"
    else:
        loc_name = loc_name[0]
#    print loc_name
    return loc_name


# Get list of locations stored in location table
def db_get_loc_list():
    global db_cur
    loc_list = []
    db_cur.execute("select name from location")
    for row in db_cur:
        loc_list.append(row[0])
#    print loc_list
    return loc_list


# Close connection to database  
def db_close():
    global db_conn
    db_conn.close()


# Convert format of data from list to string
def db_convert_list_to_str(list):
    str = ""
    for i in range(0, len(list)):
        str = str + "[\"" + list[i][0] + "," + list[i][1] + "\"]"
        if not i == len(list)-1:
            str = str + ";"
    return str

            
# Convert format of data from string to list
def db_convert_str_to_list(str):
    list = []
    str = str.split(";")
#    print str
    for line in str:
        match = re.search('"(.*)(,)(.*)"', line)
        if match:
            list.append([match.group(1),match.group(3)])
    return list
